package com.junjie.base.datasource.db;

import com.alibaba.fastjson.JSONObject;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.List;

public class JdbcTemplate {
    /**
     * 抽取获得数据连接函数
     *
     * @return 连接
     */
    public static Connection getConnect() {
        return Druid.getConnection();
    }

    /**
     * 执行数据更新SQL语句
     *
     * @param sql     SQL语句
     * @param objects SQL语句的参数
     * @return 执行成功的数量, 执行失败则为-1
     */
    public static int update(String sql, Object... objects) {
        //判断参数数量是否正确
        if (sql.length() - sql.replace("?", "").length() != objects.length) {
            System.out.println("SQL语句参数数量不对");
            return -1;
        }
        //获取连接
        Connection connection = getConnect();
        //装载sql语句,获取执行对象
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            preparedStatement = connection.prepareStatement(sql);
            //填充参数
            for (int i = 0; i < objects.length; i++) {
                try {
                    preparedStatement.setObject(i + 1, objects[i]);
                } catch (SQLException e) {
                    System.out.println("设置参数失败");
                    e.printStackTrace();
                }
            }
            count = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("获取执行对象失败");
            e.printStackTrace();
        }
        close(null, preparedStatement, connection);
        return count;
    }

    /**
     * 事务专用,执行数据更新SQL语句
     *
     * @param sql     SQL语句
     * @param objects SQL语句的参数
     * @return 执行成功的数量, 执行失败则为-1
     */
    public static boolean update(Connection connection, String sql, Object... objects) throws SQLException {
        //此为事务专用更新,关闭自动提交开启事务
        try {
            boolean autoCommit = connection.getAutoCommit();
            if (autoCommit) {
                connection.setAutoCommit(false);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //判断参数数量是否正确
        if (sql.length() - sql.replace("?", "").length() != objects.length) {
            System.out.println("SQL语句参数数量不对");
            return false;
        }
        //装载sql语句,获取执行对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //填充参数
        for (int i = 0; i < objects.length; i++) {
            preparedStatement.setObject(i + 1, objects[i]);
        }
        int count = preparedStatement.executeUpdate();
        //关闭资源
        close(null, preparedStatement, null);
        return count > 0;
    }

    /**
     * 执行查询SQL语句,只能查一条数据
     *
     * @param cla     查询的数据库的表的映射对象的类
     * @param sql     SQL语句
     * @param objects 参数列表
     * @param <T>     泛型类
     * @return 填充好数据的对象
     */
    public static <T> T selectOne(Class<?> cla, String sql, Object... objects) {
        //判断参数数量是否正确
        if (sql.length() - sql.replace("?", "").length() != objects.length) {
            System.out.println("SQL语句参数数量不对");
            return null;
        }
        //获取连接
        Connection connection = getConnect();
        //装载sql语句,获取执行对象
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Object obj = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            //填充参数
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i + 1, objects[i]);
            }
            //执行SQL语句,并获取结果集
            resultSet = preparedStatement.executeQuery();
            //遍历结果集
            Method[] methods = cla.getMethods();
            obj = cla.newInstance();
            if (resultSet != null && resultSet.next()) {
                for (Method method : methods) {
                    if (method.getName().toLowerCase().contains("set")) {
                        String name = method.getName();
                        Object o = resultSetTransform(resultSet.getObject(tof(name.substring(3))));
                        method.invoke(obj, o);
                    }
                }
                return (T) obj;
            } else {
                return null;
            }
        } catch (Exception e) {
            printExceptionInfo(e);
        } finally {
            close(resultSet, preparedStatement, connection);
        }
        return (T) obj;
    }

    /**
     * 执行查询SQL语句,查询多条
     *
     * @param sql     SQL语句
     * @param cla     查询的数据库的表的映射对象的类
     * @param objects 参数列表
     * @param <T>     泛型类
     * @return 填充好数据的列表
     */
    public static <T> List<T> selectList(String sql, Class<?> cla, Object... objects) {
        //判断参数数量是否正确
        if (sql.length() - sql.replace("?", "").length() != objects.length) {
            System.out.println("SQL语句参数数量不对");
            return null;
        }
        //获取连接
        Connection connection = getConnect();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        List<T> list = new ArrayList<>();
        //装载sql语句,获取执行对象
        try {
            preparedStatement = connection.prepareStatement(sql);
            //填充参数
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i + 1, objects[i]);
            }
            //执行SQL语句,并获取结果集
            resultSet = preparedStatement.executeQuery();
            //遍历结果集
            Method[] methods = cla.getMethods();
            //创建返回结果集容器
            while (resultSet != null && resultSet.next()) {
                Object obj = cla.newInstance();
                for (Method method : methods) {
                    if (method.getName().toLowerCase().contains("set")) {
                        String name = method.getName();
                        Object o = resultSetTransform(resultSet.getObject(tof(name.substring(3))));
                        method.invoke(obj, o);
                    }
                }
                list.add((T) obj);
            }
        } catch (Exception e) {
            printExceptionInfo(e);
        } finally {
            close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 返回json数据
     * DDL操作模板
     *
     * @param sql
     * @param arguments
     * @return
     */
    public static List<JSONObject> selectListForJSON(String sql, Object... arguments) {
        ResultSet rSet = null;
        //处理器来确定T的类型
        BeanHandler<JSONObject> handler = new BeanHandler<>(JSONObject.class);
        try (
                Connection connection = Druid.getConnection();
                PreparedStatement pStatement = connection.prepareStatement(sql)
        ) {
            if (arguments != null && arguments.length > 0) {
                for (int i = 0; i < arguments.length; i++) {
                    pStatement.setObject(i + 1, arguments[i]);
                }
            }
            // 调用处理结果集类对数据库查询结果集进行装箱
            rSet = pStatement.executeQuery();
            return handler.handlerJson(rSet);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 输入查询语句返回,查询出来的数据的数量
     *
     * @param sql     SQL语句
     * @param objects 参数
     * @return 数据的数量
     */
    public static int queryCount(String sql, Object... objects) {
        //判断参数数量是否正确
        if (sql.length() - sql.replace("?", "").length() != objects.length) {
            System.out.println("SQL语句参数数量不对");
            return -1;
        }
        //获取连接
        Connection connection = getConnect();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        int rs = 0;
        //装载sql语句,获取执行对象
        try {
            preparedStatement = connection.prepareStatement(sql);
            //填充参数
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i + 1, objects[i]);
            }
            //执行SQL语句,并获取结果集
            resultSet = preparedStatement.executeQuery();
            //遍历结果集
            //创建返回结果集容器
            while (resultSet != null && resultSet.next()) {
                rs++;
            }
        } catch (Exception e) {
            printExceptionInfo(e);
        } finally {
            close(resultSet, preparedStatement, connection);
        }
        return rs;
    }

    /**
     * 此函数通过获取第一条数据的count字段获取结果返回数据的数量
     *
     * @param sql     SQL语句
     * @param objects 参数列表
     * @return 数据总数
     */
    public static int getCount(String sql, Object... objects) {
        //判断参数数量是否正确
        if (sql.length() - sql.replace("?", "").length() != objects.length) {
            System.out.println("SQL语句参数数量不对");
            return -1;
        }
        //获取连接
        Connection connection = getConnect();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        int rs = -1;
        //装载sql语句,获取执行对象
        try {
            preparedStatement = connection.prepareStatement(sql);
            //填充参数
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i + 1, objects[i]);
            }
            //执行SQL语句,并获取结果集
            resultSet = preparedStatement.executeQuery();
            //遍历结果集
            //创建返回结果集容器
            if (resultSet != null && resultSet.next()) {
                rs = resultSet.getInt("count");
            }
        } catch (Exception e) {
            printExceptionInfo(e);
        } finally {
            close(resultSet, preparedStatement, connection);
        }
        return rs;
    }

    /**
     * 通用的插入Dao
     *
     * @param o 数据对象
     * @return 数据修改数量
     */
    public static <T> int insert(T o) {
        Class<?> aClass = o.getClass();
        String filedByClass = getFiledByClass(aClass);
        String[] filedList = filedByClass.split(",");
        StringBuilder s = new StringBuilder();
        StringBuilder rsFiledByClass = new StringBuilder();
        String sql = "INSERT INTO `" + classTransitionTabName(aClass);
        List<Object> parameterList = new ArrayList<>();
        for (String s1 : filedList) {
            Object o1;
            try {
                Method method = aClass.getMethod("get" + unTof(s1));
                o1 = method.invoke(o);
                if (o1 != null) {
                    parameterList.add(o1);
                    s.append("?,");
                    rsFiledByClass.append(s1).append(",");
                }
            } catch (Exception e) {
                printExceptionInfo(e);
            }
        }
        s = new StringBuilder(s.substring(0, s.length() - 1));
        //拼接sql
        sql += "`(" + rsFiledByClass.substring(0, rsFiledByClass.length() - 1) + ") VALUES(" + s + ")";
        int update = update(sql, parameterList.toArray());
        if (update > 0) {
            System.out.println("执行成功");
        } else {
            System.out.println("执行失败");
        }
        return update;
    }

    /**
     * 事务专用的插入Dao
     *
     * @param o 数据对象
     * @return 数据修改数量
     */
    public static <T> boolean insert(Connection connection, T o) throws SQLException {
        Class<?> aClass = o.getClass();
        String filedByClass = getFiledByClass(aClass);
        String[] filedList = filedByClass.split(",");
        StringBuilder s = new StringBuilder();
        StringBuilder rsFiledByClass = new StringBuilder();
        String sql = "INSERT INTO `" + classTransitionTabName(aClass);
        List<Object> parameterList = new ArrayList<>();
        for (String s1 : filedList) {
            Object o1;
            try {
                Method method = aClass.getMethod("get" + unTof(s1));
                o1 = method.invoke(o);
                if (o1 != null) {
                    parameterList.add(o1);
                    s.append("?,");
                    rsFiledByClass.append(s1).append(",");
                }
            } catch (Exception e) {
                printExceptionInfo(e);
            }
        }
        s = new StringBuilder(s.substring(0, s.length() - 1));
        //拼接sql
        sql += "`(" + rsFiledByClass.substring(0, rsFiledByClass.length() - 1) + ") VALUES(" + s + ")";
        return update(connection, sql, parameterList.toArray());
    }

    /**
     * 通过的根据id删除数据的Dao
     *
     * @param cla 类型
     * @param id  id
     * @return 修改数据行数
     */
    public static int deleteById(Class<?> cla, long id) {
        // 创建SQL语句
        String sql = "DELETE FROM `" + classTransitionTabName(cla) + "` WHERE `id` = ?";
        // 执行SQL语句
        int update = update(sql, id);
        if (update > 0) {
            System.out.println("执行成功");
        } else {
            System.out.println("执行失败");
        }

        return update;
    }

    /**
     * 通用的修改Dao
     *
     * @param o   POJO对象
     * @param <T> 泛型
     * @return 修改数据的行数
     */
    public static <T> int updateById(T o) {
        Class<?> aClass = o.getClass();
        String filedByClass = getFiledByClass(aClass);
        String[] filedList = filedByClass.split(",");
        StringBuilder s = new StringBuilder();
        // 创建SQL语句
        String sql = "UPDATE `" + classTransitionTabName(o.getClass()) + "` SET `id`= `id`,";
        // 装载参数
        List<Object> parameterList = new ArrayList<>();
        for (String s1 : filedList) {
            Object o1;
            try {
                Method method = aClass.getMethod("get" + unTof(s1));
                o1 = method.invoke(o);
                if (o1 != null) {
                    parameterList.add(o1);
                    s.append(s1).append(" = ?,");
                }
            } catch (Exception e) {
                printExceptionInfo(e);
            }
        }
        try {
            try {
                parameterList.add(aClass.getMethod("getId").invoke(o));
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
        if (parameterList.size() > 0) {
            s = new StringBuilder(s.substring(0, s.length() - 1));
            sql += s;
        }
        sql += " WHERE `id` = ?";
        //执行SQL语句
        int update = update(sql, parameterList.toArray());
        if (update > 0) {
            System.out.println("执行成功");
        } else {
            System.out.println("执行失败");
        }
        return update;
    }

    /**
     * 通用的修改Dao
     *
     * @param o   POJO对象
     * @param <T> 泛型
     * @return 修改数据的行数
     */
    public static <T> boolean updateById(Connection connection, T o) throws SQLException {
        Class<?> aClass = o.getClass();
        String filedByClass = getFiledByClass(aClass);
        String[] filedList = filedByClass.split(",");
        StringBuilder s = new StringBuilder();
        // 创建SQL语句
        String sql = "UPDATE `" + classTransitionTabName(o.getClass()) + "` SET `id`= `id`,";
        // 装载参数
        List<Object> parameterList = new ArrayList<>();
        for (String s1 : filedList) {
            Object o1;
            try {
                Method method = aClass.getMethod("get" + unTof(s1));
                o1 = method.invoke(o);
                if (o1 != null) {
                    parameterList.add(o1);
                    s.append(s1).append(" = ?,");
                }
            } catch (Exception e) {
                printExceptionInfo(e);
            }
        }
        try {
            try {
                parameterList.add(aClass.getMethod("getId").invoke(o));
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
        if (parameterList.size() > 0) {
            s = new StringBuilder(s.substring(0, s.length() - 1));
            sql += s;
        }
        sql += " WHERE `id` = ?";
        //执行SQL语句
        return update(connection, sql, parameterList.toArray());
    }

    /**
     * 通用的根据id查询Dao
     *
     * @param cla 类型
     * @param id  查询数据的id
     * @param <T> 泛型
     * @return 返回一个填充好的数据对象
     */
    public static <T> T findById(Class<?> cla, long id) {
        return selectOne(cla, "SELECT * FROM `" + classTransitionTabName(cla) + "` WHERE `id` = ?", id);
    }

    /**
     * 通用的查询所有的Dao
     *
     * @param cla 查询的类型
     * @param <T> 泛型
     * @return 填充好数据的列表
     */
    public static <T> List<T> findAll(Class<?> cla) {
        return selectList("SELECT * FROM `" + classTransitionTabName(cla) + "`", cla);
    }


    /**
     * 通用的模糊搜索Dao
     *
     * @param o   数据对象
     * @param <T> 泛型
     * @return 填充好数据的列表
     */
    public static <T> List<T> search(T o) {
        Class<?> cla = o.getClass();
        String filedByClass = getFiledByClass(cla);
        String[] filedList = filedByClass.split(",");
        StringBuilder s = new StringBuilder();
        String sql = "SELECT * FROM `" + classTransitionTabName(cla) + "` WHERE 1 = 1";
        // 装载参数
        List<Object> parameterList = new ArrayList<>();
        for (String s1 : filedList) {
            Object o1;
            try {
                Method method = cla.getMethod("get" + unTof(s1));
                o1 = method.invoke(o);
                if (o1 != null) {
                    s.append(s1).append(" LIKE ? OR ");
                    parameterList.add("%" + o1 + "%");
                }
            } catch (Exception e) {
                printExceptionInfo(e);
            }
        }
        if (parameterList.size() > 0) {
            s = new StringBuilder(s.substring(0, s.length() - 3));
            sql += " AND " + s;
        }
        return selectList(sql, cla, parameterList.toArray());
    }

    /**
     * 通用的条件查询Dao
     *
     * @param o   数据对象
     * @param <T> 泛型
     * @return 填充好数据的列表
     */
    public static <T> List<T> termSearch(T o) {
        Class<?> cla = o.getClass();
        String filedByClass = getFiledByClass(cla);
        String[] filedList = filedByClass.split(",");
        StringBuilder s = new StringBuilder();
        String sql = "SELECT * FROM `" + classTransitionTabName(cla) + "` WHERE 1 = 1";
        // 装载参数
        List<Object> parameterList = new ArrayList<>();
        for (String s1 : filedList) {
            Object o1;
            try {
                Method method = cla.getMethod("get" + unTof(s1));
                o1 = method.invoke(o);
                if (o1 != null) {
                    s.append(s1).append(" = ? AND ");
                    parameterList.add(o1);
                }
            } catch (Exception e) {
                printExceptionInfo(e);
            }
        }
        if (parameterList.size() > 0) {
            s = new StringBuilder(s.substring(0, s.length() - 4));
            sql += " AND " + s;
        }
        return selectList(sql, cla, parameterList.toArray());
    }

    /**
     * 查询数据总数
     *
     * @return 数据总数
     */
    public static int selectCount(Class<?> cla) {
        return getCount("SELECT COUNT(*) AS count  FROM `" + classTransitionTabName(cla) + "`");
    }

    /**
     * 吧结果集中的数据进行类型转换, 因为整形只用Long
     *
     * @return 数据
     */
    static Object resultSetTransform(Object o) {
        /*if (o instanceof Integer) {
            return ((Integer) o).longValue();
        } else if (o instanceof LocalDateTime) {
            return new Timestamp(((LocalDateTime) o).toInstant(ZoneOffset.of("+8")).toEpochMilli());
        } else if (o instanceof BigDecimal) {
            return ((BigDecimal) o).doubleValue();
        } else {
            return o;
        }*/
        return o;
    }


    /**
     * 通过类型转换成对应的表名,需要遵守一定的规范才能生效
     *
     * @param cla POJO类型
     * @return 表名
     */
    public static String classTransitionTabName(Class<?> cla) {
        if (cla.isAnnotationPresent(TableName.class)) {
            TableName tableName = cla.getAnnotation(TableName.class);
            String value = tableName.value();
            if (!"".equals(value)) {
                return value;
            }
        }
        String className = tof(cla.getName());
        String[] split = className.split("\\.");
        className = split[split.length - 1];
        return className;
    }


    /**
     * 转换驼峰命名法
     *
     * @param s 要转换的字符串
     * @return 驼峰的字符串
     */
    public static String tof(String s) {
        StringBuilder rs = new StringBuilder();
        int count = 0;
        for (int i = 0; i < s.length(); i++) {
            char c = s.charAt(i);
            if (c >= 65 && c <= 90) {
                if (count == 0) {
                    count++;
                    rs.append((char) (c + 32));
                } else {
                    rs.append("_").append((char) (c + 32));
                }
            } else {
                rs.append(c);
            }
        }
        return rs.toString();
    }

    /**
     * 打印异常信息
     *
     * @param e 异常
     */
    public static void printExceptionInfo(Exception e) {
        System.out.println("发生错误:" + e.getClass().toString().replace("class ", "") + "\n错误原因:" + e.getMessage());
        e.printStackTrace();
    }

    /**
     * 驼峰命名法转函数名
     *
     * @param s 要转换的字符串
     * @return 字符串
     */
    public static String unTof(String s) {
        StringBuilder rs = new StringBuilder();
        s = s.replaceAll("[^a-zA-Z_]", "");
        for (int i = 0; i < s.length(); i++) {
            char c = s.charAt(i);
            if (i == 0) {
                rs.append((char) (c - 32));
            } else if (c == '_') {
                rs.append((char) (s.charAt(i + 1) - 32));
                i++;
            } else {
                rs.append(c);
            }
        }
        return rs.toString();
    }

    /**
     * 通过类的set/get获取 字段列表
     *
     * @param cls        类型
     * @param usePrimary 是否使用id
     * @return 表字段名
     */
    public static String getFiledByClass(Class<?> cls, boolean usePrimary) {
        StringBuilder stringBuffer = new StringBuilder();
        Method[] methods = cls.getMethods();
        for (Method method : methods) {
            String methodName = method.getName();
            if (!usePrimary) {
                if (methodName.contains("set")) {
                    if (!"id".equals(tof(methodName.substring(3)))) {
                        //如果是set方法获取字段名进行驼峰解析
                        stringBuffer.append("`").append(tof(methodName.substring(3))).append("`");
                        stringBuffer.append(",");
                    }
                }
            } else {
                if (methodName.contains("set")) {
                    //如果是set方法获取字段名进行驼峰解析
                    stringBuffer.append("`").append(tof(methodName.substring(3))).append("`");
                    stringBuffer.append(",");
                }
            }
        }
        return stringBuffer.substring(0, stringBuffer.toString().length() - 1);
    }

    /**
     * 通过类的set/get获取 字段列表,此函数默认没有id
     *
     * @param cls 类型
     * @return 字符串
     */
    public static String getFiledByClass(Class<?> cls) {
        return getFiledByClass(cls, false);
    }

    /**
     * 关闭资源
     *
     * @param resultSet  结果集
     * @param statement  执行对象
     * @param connection 数据库连接
     * @return 返回释放结果
     */
    public static boolean close(ResultSet resultSet, Statement statement, Connection connection) {
        boolean flag = true;
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
                flag = false;
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
                flag = false;
            }
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
                flag = false;
            }
        }

        return flag;
    }

}
